package com.caiyi.financial.nirvana.sms.controller;


import com.caiyi.financial.nirvana.sms.entity.SmsAdmin;
import com.caiyi.financial.nirvana.sms.jedis.JedisKey;
import com.caiyi.financial.nirvana.sms.jedis.JedisService;
import com.caiyi.financial.nirvana.sms.querybean.GatewayDayQueryBean;
import com.caiyi.financial.nirvana.sms.querybean.GatewayMonthQueryBean;
import com.caiyi.financial.nirvana.sms.querybean.MsgDayQueryBean;
import com.caiyi.financial.nirvana.sms.querybean.MsgMonthQueryBean;
import com.caiyi.financial.nirvana.sms.service.SmsMsgService;
import com.caiyi.financial.nirvana.sms.vo.SmsPriceVo;
import com.youyu.common.api.Result;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiImplicitParam;
import io.swagger.annotations.ApiImplicitParams;
import io.swagger.annotations.ApiOperation;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.math.NumberUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.shiro.SecurityUtils;
import org.apache.shiro.subject.Subject;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.web.bind.annotation.*;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

@Api(description = "统计管理")
@CrossOrigin
@RestController
@RequestMapping("sms/admin/msgstatistic")
public class MsgStatisticController {

    @Autowired
    private SmsMsgService msgService;

    @Value("${caiyi.company.accounts}")
    private String caiyiAccounts;

    @Value("${finance.company.accounts}")
    private String financeAccounts;


    @ApiOperation(value = "根据客户分析日短信发送详情", notes = "根据客户分析日短信发送详情描述")
    @ApiImplicitParam(name = "bean", value = "客户日统计查询参数", required = true, dataType = "MsgDayQueryBean", paramType = "body")
    @PostMapping("/getAccountMsgByDayByParams")
    public Result getMsgByDayByAccount(@RequestBody MsgDayQueryBean bean) {
        String account = getAccount();
        if (StringUtils.isNotBlank(getAccount())) {
            bean.setAccount(account);
        }
        return Result.ok(msgService.getMsgByDayByAccount(bean));
    }

    @ApiOperation(value = "根据客户分析月短信发送详情", notes = "根据客户分析月短信发送详情描述")
    @ApiImplicitParam(name = "bean", value = "客户月统计查询参数", required = true, dataType = "MsgMonthQueryBean", paramType = "body")
    @PostMapping("/getAccountMsgByMonthByParams")
    public Result geMsgByMonthAccount(@RequestBody MsgMonthQueryBean bean) {
        String account = getAccount();
        if (StringUtils.isNotBlank(getAccount())) {
            bean.setAccount(account);
        }
        return Result.ok(msgService.getMsgByMonthByAccount(bean));
    }

    @ApiOperation(value = "根据网关分析日短信发送详情", notes = "根据网关分析日短信发送详情描述")
    @ApiImplicitParam(name = "bean", value = "网关日统计查询参数", required = true, dataType = "GatewayDayQueryBean", paramType = "body")
    @PostMapping("/getGatewayMsgByDayByParams")
    public Result getMsgByDayByGateway(@RequestBody GatewayDayQueryBean bean) {
        return Result.ok(msgService.getMsgByDayByGateway(bean));
    }


    @ApiOperation(value = "根据网关分析月短信发送详情", notes = "根据网关分析月短信发送详情描述")
    @ApiImplicitParam(name = "bean", value = "网关月统计查询参数", required = true, dataType = "GatewayMonthQueryBean", paramType = "body")
    @PostMapping("/getGatewayMsgByMonthByParams")
    public Result getMsgByMonthByGateway(@RequestBody GatewayMonthQueryBean bean) {
        return Result.ok(msgService.getMsgByMonthByGateway(bean));
    }


    @ApiOperation(value = "分析系统日短信发送详情", notes = "分析系统日短信发送详情描述")
    @ApiImplicitParam(name = "bean", value = "系统日统计查询参数", required = true, dataType = "MsgDayQueryBean", paramType = "body")
    @PostMapping("/getSystemMsgByDayByParams")
    public Result getMsgByDayBySystem(@RequestBody MsgDayQueryBean bean) {
        String account = getAccount();
        if (StringUtils.isNotBlank(getAccount())) {
            bean.setAccount(account);
        }
        return Result.ok(msgService.getMsgByDayBySystem(bean));
    }

    @ApiOperation(value = "分析系统月短信发送详情", notes = "分析系统月短信发送详情描述")
    @ApiImplicitParam(name = "bean", value = "系统月统计查询参数", required = true, dataType = "MsgMonthQueryBean", paramType = "body")
    @PostMapping("/getSystemMsgByMonthByParams")
    public Result getMsgByMonthBySystem(@RequestBody MsgMonthQueryBean bean) {
        String account = getAccount();
        if (StringUtils.isNotBlank(getAccount())) {
            bean.setAccount(account);
        }
        return Result.ok(msgService.getMsgByMonthBySystem(bean));
    }

    @ApiOperation(value = "日账单明细", notes = "日账单明细")
    @ApiImplicitParams({
            @ApiImplicitParam(name = "date", value = "时间", dataType = "String", paramType = "query", required = true),
            @ApiImplicitParam(name = "account", value = "用户账号", dataType = "String", paramType = "query", required = true)})
    @PostMapping("/getDayBillDetail")
    public Result getDayBillDetail(String date, String account) {
        return Result.ok(msgService.getDayBillDetail(date, account));
    }

    @ApiOperation(value = "月账单明细", notes = "月账单明细")
    @ApiImplicitParams({
            @ApiImplicitParam(name = "date", value = "时间", dataType = "String", paramType = "query", required = true),
            @ApiImplicitParam(name = "account", value = "用户账号", dataType = "String", paramType = "query", required = true)})
    @PostMapping("/getMonthBillDetail")
    public Result getMonthBillDetail(String date, String account) {
        return Result.ok(msgService.getMonthBillDetail(date, account));
    }


    @ApiOperation(value = "分析系统月短信发送详情账单", notes = "分析系统月短信发送详情账单")
    @ApiImplicitParam(name = "month", value = "月份", required = true, dataType = "String", paramType = "query")
    @RequestMapping("/getMsgByMonthBySystemBill")
    public void getMsgByMonthBySystemBill(@RequestParam String month, HttpServletResponse response) {
        int rowIndex = 3;
        Map dataMap = msgService.getMsgByMonthBySystemBill(month, caiyiAccounts, financeAccounts);
        Map caiyiMap = (Map) dataMap.get("caiyiMap");
        Map financeMap = (Map) dataMap.get("financeMap");
        Map otherMap = (Map) dataMap.get("otherMap");
        //获取通道单价
        List<SmsPriceVo> priceList = (List<SmsPriceVo>) dataMap.get("list");
        List<Map> accountMap = (List<Map>) dataMap.get("accountList");
        //创建HSSFWorkbook对象(excel的文档对象)
        HSSFWorkbook wb = new HSSFWorkbook();
        //建立新的sheet对象（excel的表单）
        HSSFSheet sheet = wb.createSheet("报表");


        sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 1));
        sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, 1));
        sheet.addMergedRegion(new CellRangeAddress(3, caiyiMap.entrySet().size() * 2 + 3 - 1, 0, 0));
        //计算合并空格数量，从3行开始
        int caiyiStart = 3 + caiyiMap.entrySet().size() * 2;
        int caiyiEnd = caiyiStart + financeMap.entrySet().size() * 2 - 1;
//        int otherStart = caiyiEnd + 1;
//        int otherEnd = otherStart + otherMap.entrySet().size() * 2 - 1;
        sheet.addMergedRegion(new CellRangeAddress(caiyiStart, caiyiEnd, 0, 0));
        //sheet.addMergedRegion(new CellRangeAddress(otherStart, otherEnd, 0, 0));
        sheet.addMergedRegion(new CellRangeAddress(3, caiyiMap.entrySet().size() * 2 + 3 - 1, priceList.size() + 3, priceList.size() + 3));
        sheet.addMergedRegion(new CellRangeAddress(caiyiStart, caiyiEnd, priceList.size() + 3, priceList.size() + 3));
        //sheet.addMergedRegion(new CellRangeAddress(otherStart, otherEnd, priceList.size() + 3, priceList.size() + 3));

        sheet.setZoom(8, 7);
        sheet.setDefaultColumnWidth(13);


        HSSFCellStyle style = wb.createCellStyle();
        HSSFFont font = wb.createFont();
        font.setFontName("宋体");//设置字体名称
        font.setFontHeightInPoints((short) 10);//设置字号
        font.setColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());//设置字体颜色
        HSSFCellStyle style1 = wb.createCellStyle();
        HSSFFont font1 = wb.createFont();
        font1.setFontName("宋体");//设置字体名称
        font1.setFontHeightInPoints((short) 10);//设置字号
        font1.setColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());//设置字体颜色


        HSSFRow row1 = sheet.createRow(0);
        row1.setZeroHeight(true);

        HSSFRow row2 = sheet.createRow(1);

        row2.createCell(0).setCellValue("业务");

        HSSFRow row3 = sheet.createRow(2);
        row3.createCell(0).setCellValue("通道单价");

        int colIndex = 2;
        for (SmsPriceVo smsPriceVo : priceList) {
            row1.createCell(colIndex).setCellValue(smsPriceVo.getGatewayId());
            row2.createCell(colIndex).setCellValue(smsPriceVo.getName());
            row3.createCell(colIndex).setCellValue(NumberUtils.toDouble(smsPriceVo.getPrice()));
            colIndex++;
        }

        rowIndex = getListDateExcel("彩票（彩亿信息主体）", caiyiMap, sheet, priceList, row1, row3, rowIndex);
        getListDateExcel("金融（互金主体）", financeMap, sheet, priceList, row1, row3, rowIndex);
        //getListDateExcel("其它", otherMap, sheet, priceList, row1, row3, rowIndex);

        row2.createCell(priceList.size() + 2).setCellValue("合计");

        row2.createCell(priceList.size() + 3).setCellValue("主体合计");

        HSSFRow totalNumRow = sheet.createRow(caiyiEnd + 1);
        sheet.createRow(caiyiEnd + 2).createCell(1).setCellValue("总金额合计");
        double totalNum = 0;
        BigDecimal totalPrice = new BigDecimal("0");
        totalNumRow.createCell(1).setCellValue("总条数合计");


        for (int i = 3; i < totalNumRow.getRowNum(); i++) {
            for (int j = 2; j < priceList.size() + 2; j++) {
                Cell cell = sheet.getRow(i).getCell(j);
                cell.setCellType(CellType.STRING);
                String num = cell.getStringCellValue();
                if (i == totalNumRow.getRowNum()) {
                    break;
                }
                cell = sheet.getRow(i + 1).getCell(j);
                cell.setCellType(CellType.STRING);
                String price = cell.getStringCellValue();
                totalNum = totalNum + NumberUtils.toInt(num);
                totalPrice = totalPrice.add(new BigDecimal(price).setScale(2, BigDecimal.ROUND_DOWN));
            }
            sheet.getRow(i).createCell(priceList.size() + 2).setCellValue(totalNum);
            sheet.getRow(i + 1).createCell(priceList.size() + 2).setCellValue(totalPrice.doubleValue());
            totalNum = 0;
            totalPrice = new BigDecimal("0");
            i++;
        }

        for (int j = 2; j < priceList.size() + 3; j++) {
            for (int i = 3; i < totalNumRow.getRowNum(); i++) {
                Cell cell = sheet.getRow(i).getCell(j);
                cell.setCellType(CellType.STRING);
                String num = cell.getStringCellValue();
                if (i == totalNumRow.getRowNum()) {
                    break;
                }
                cell = sheet.getRow(i + 1).getCell(j);
                cell.setCellType(CellType.STRING);
                String price = cell.getStringCellValue();
                totalNum = totalNum + NumberUtils.toInt(num);
                totalPrice = totalPrice.add(new BigDecimal(price).setScale(2, BigDecimal.ROUND_DOWN));
                i++;
            }
            sheet.getRow(caiyiEnd + 1).createCell(j).setCellValue(totalNum);
            sheet.getRow(caiyiEnd + 2).createCell(j).setCellValue(totalPrice.doubleValue());
            totalNum = 0;
            totalPrice = new BigDecimal("0");
        }

        for (int i = 0; i <= sheet.getLastRowNum(); i++) {
            Row temp = sheet.getRow(i);
            temp.setHeight((short) 400);
            Iterator<Cell> srcCells = temp.cellIterator();
            while (srcCells.hasNext()) {
                Cell srcCell = srcCells.next();
                style.setFont(font);
                style.setWrapText(true);
                srcCell.setCellStyle(style);
            }
        }
        Iterator<Cell> srcCell2 = row2.cellIterator();
        while (srcCell2.hasNext()) {
            font1.setBold(true);
            style1.setFont(font1);
            Cell srcCell = srcCell2.next();
            srcCell.setCellStyle(style1);
        }
        row2.setHeight((short) 450);
        //输出Excel文件
        OutputStream output = null;
        try {
            output = response.getOutputStream();
            response.reset();
            response.setHeader("Content-disposition", "attachment; filename=" + month + ".xls");//文件名这里可以改
            response.setContentType("application/msexcel");
            wb.write(output);
            output.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    private int getListDateExcel(String text, Map map, HSSFSheet sheet, List<SmsPriceVo> priceList, HSSFRow row1, HSSFRow row3, int rowIndex) {
        BigDecimal totalPrice = new BigDecimal("0");
        for (Object key : map.keySet()) {
            String[] arr = key.toString().split(":");
            HSSFRow tempRow = sheet.createRow(rowIndex);
            HSSFRow tempRow1 = sheet.createRow(rowIndex + 1);
            tempRow.createCell(0).setCellValue(text);
            tempRow.createCell(1).setCellValue(arr[0] + "\r\n(" + arr[1] + ")");
            sheet.addMergedRegion(new CellRangeAddress(tempRow1.getRowNum() - 1, tempRow1.getRowNum(), 1, 1));
            rowIndex += 2;
            List<SmsPriceVo> countList = (List<SmsPriceVo>) map.get(key);
            for (int i = 2; i < priceList.size() + 2; i++) {
                Cell cell = tempRow.createCell(i);
                cell.setCellType(CellType.NUMERIC);
                cell.setCellValue(0.0);
                tempRow1.createCell(i).setCellValue(0.0);
                int gateWayId = new Double(row1.getCell(i).getNumericCellValue()).intValue();
                double price = row3.getCell(i).getNumericCellValue();
                for (SmsPriceVo smsPriceVo : countList) {
                    if (gateWayId == smsPriceVo.getGatewayId()) {
                        Double msgCount = NumberUtils.toDouble(smsPriceVo.getMsgCount() + "");
                        tempRow.createCell(i).setCellValue(msgCount);
                        double rowPrice = new BigDecimal(price * msgCount).setScale(2, BigDecimal.ROUND_DOWN).doubleValue();
                        tempRow1.createCell(i).setCellValue(rowPrice);
                        totalPrice = totalPrice.add(new BigDecimal(rowPrice).setScale(2, BigDecimal.ROUND_DOWN));
                        break;
                    }
                }
            }
        }
        sheet.getRow(rowIndex - map.keySet().size() * 2).createCell(priceList.size() + 3).setCellValue(totalPrice.doubleValue());
        return rowIndex;
    }

    /**
     * 获取session中的用户，如果是客户返回账号否则为空
     *
     * @return
     */
    private String getAccount() {
        Subject subject = SecurityUtils.getSubject();
        SmsAdmin user = (SmsAdmin) subject.getPrincipal();
        //获取客户账号是否存在，客户账号只能查询属于自己的短信列表
        String key = String.format(JedisKey.SMS_USER, user.getAccount());
        if (JedisService.exists(key)) {
            return user.getAccount();
        }
        return "";
    }


}
